#include "sqlitemanage.h"


SqliteManage::SqliteManage(const QString &connectionName) {
    m_connectionName = connectionName.isEmpty() ?
    QString("SQLiteConnection_%1").arg(quintptr(this)) :connectionName;
    m_db = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);

}

SqliteManage::~SqliteManage()
{
    if (m_db.isOpen()) {
            m_db.close();
        }
    QSqlDatabase::removeDatabase(m_connectionName);

}

bool SqliteManage::openDatabase(const QString &dbName)
{
    m_db.setDatabaseName(dbName);
    if (!m_db.open()) {
        m_lastError = m_db.lastError().text();
        qDebug() << "Open database failed:" << m_lastError;
        return false;
    }
    return true;
}

void SqliteManage::closeDatabase()
{
    if (m_db.isOpen()) {
        m_db.close();
    }
}


//QStringList list;
//list<<"id"<<"name"<<"age";
//createTable("student",list);
bool SqliteManage::createTable(const QString &tableName, const QStringList &fields)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (fields.isEmpty()) {
        m_lastError = "Fields list is empty";
        return false;
    }

    QString sql = QString("CREATE TABLE IF NOT EXISTS %1 (%2)").arg(tableName).arg(fields.join(","));

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Create table failed:" << m_lastError;
        return false;
    }

    return true;
}

//QVariantMap:QVariant类型的QMap，一次性存储一条记录的所有信息
//eg.<"id",11>,<"name","zhangsan">,<"age",12>
bool SqliteManage::insertData(const QString &tableName, const QVariantMap &data)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (data.isEmpty()) {
        m_lastError = "Data is empty";
        return false;
    }

    QStringList fields = data.keys();//fields:["id","name",,"age"]
    QStringList placeholders;
    for (int i = 0; i < fields.size(); ++i) {
        placeholders.append("?");//QStringList placeholders----> ["?","?","?"]
    }

    //↓↓↓↓↓↓INSERT INTO tableName (id,name,phone...) VALUES (?,?,?...)↓↓↓↓↓↓↓↓
    QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
                     .arg(tableName)
                     .arg(fields.join(","))
                     .arg(placeholders.join(","));//join方法:将字符串列表连接成单个字符串,使用","隔开

    QSqlQuery query(m_db);
    query.prepare(sql);

    foreach (const QString &field, fields) {
        query.addBindValue(data.value(field));
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "Insert data failed:" << m_lastError;
        return false;
    }

    return true;
}

bool SqliteManage::deleteData(const QString &tableName, const QString &condition)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QString sql = QString("DELETE FROM %1").arg(tableName);
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Delete data failed:" << m_lastError;
        return false;
    }

    return true;
}

bool SqliteManage::updateData(const QString &tableName, const QVariantMap &data, const QString &condition)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (data.isEmpty()) {
        m_lastError = "Data is empty";
        return false;
    }

    QStringList setClauses;
    QStringList fields = data.keys();
    foreach (const QString &field, fields) {
        setClauses.append(QString("%1=?").arg(field));
    }

    QString sql = QString("UPDATE %1 SET %2").arg(tableName).arg(setClauses.join(","));
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }

    QSqlQuery query(m_db);
    query.prepare(sql);

    foreach (const QString &field, fields) {
        query.addBindValue(data.value(field));
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "Update data failed:" << m_lastError;
        return false;
    }

    return true;
}

QList<QVariantMap> SqliteManage::selectData(const QString &tableName, const QString &condition, const QStringList &fields)
{
    QList<QVariantMap> result;

    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return result;
    }

    QString fieldList = fields.isEmpty() ? "*" : fields.join(",");
    QString sql = QString("SELECT %1 FROM %2").arg(fieldList).arg(tableName);
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }
    qDebug() << sql;
    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Select data failed:" << m_lastError;
        return result;
    }

    while (query.next()) {
        QVariantMap record;
        for (int i = 0; i < query.record().count(); ++i) {
            record.insert(query.record().fieldName(i), query.value(i));
        }
        result.append(record);
    }

    return result;
}

bool SqliteManage::executeSql(const QString &sql)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Execute SQL failed:" << m_lastError;
        return false;
    }

    return true;
}

QString SqliteManage::lastError() const
{
    return m_lastError;
}

bool SqliteManage::tableExists(const QString &tableName)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QString sql = QString("SELECT name FROM sqlite_master WHERE type='table' AND name='%1'").arg(tableName);
    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        return false;
    }

    return query.next();
}

QSqlDatabase SqliteManage::getDB()
{
    return m_db;
}

QList<SerialConfig> SqliteManage::getAllSerialConfigs()
{
    QList<SerialConfig> configs;
    QSqlQuery query(m_db);
    //QSqlQuery query("SELECT device_id, name, port_name, baud_rate, data_bits, parity, stop_bits, flow_control FROM device_settings");
    if (!query.exec("SELECT id, device_id,name, port_name, baud_rate, data_bits, parity, stop_bits, flow_control FROM device_settings")) {
            qDebug() << "failed:" << query.lastError().text();  // 打印错误信息
            return configs;  // 返回空列表
        }
    while (query.next()) {
        SerialConfig config;
        config.id = query.value(0).toInt();
        config.device_id = query.value(1).toInt();
        config.name = query.value(2).toString();
        config.portName = query.value(3).toString();
        config.baudRate = query.value(4).toInt();
        config.dataBits = static_cast<QSerialPort::DataBits>(query.value(5).toInt());
        config.parity = static_cast<QSerialPort::Parity>(query.value(6).toInt());
        config.stopBits = static_cast<QSerialPort::StopBits>(query.value(7).toInt());
        config.flowControl = static_cast<QSerialPort::FlowControl>(query.value(8).toInt());
        configs.append(config);
    }
    qDebug()<<"Has get the Data";
    for(SerialConfig &s:configs){
        qDebug()<<"Data:"<<s.name;
    }
    return configs;
}

bool SqliteManage::getDeviceIdByName(const QString &name, int &id) {
    QSqlQuery query(m_db);
    query.prepare("SELECT device_id FROM devices WHERE name = :name");
    query.bindValue(":name", name);

    if (!query.exec()) {
        qDebug() << "Query failed:" << query.lastError().text();
        return false;
    }

    if (query.next()) {  // 检查是否有结果
        id = query.value(0).toInt();
        return true;
    } else {
        return false;  // 无匹配记录
    }
}


bool SqliteManage::addSerialConfig(const SerialConfig &config)
{
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO device_settings (device_id,name, port_name, baud_rate, data_bits, parity, stop_bits, flow_control) "
                  "VALUES (:device_id, :name, :port_name, :baud_rate, :data_bits, :parity, :stop_bits, :flow_control)");
    query.bindValue(":device_id", config.device_id);
    query.bindValue(":name", config.name);
    query.bindValue(":port_name", config.portName);
    query.bindValue(":baud_rate", config.baudRate);
    query.bindValue(":data_bits", static_cast<int>(config.dataBits));
    query.bindValue(":parity", static_cast<int>(config.parity));
    query.bindValue(":stop_bits", static_cast<int>(config.stopBits));
    query.bindValue(":flow_control", static_cast<int>(config.flowControl));
    return query.exec();
}

bool SqliteManage::deleteSerialConfig(int id)
{
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM device_settings WHERE id = :id");
    query.bindValue(":id", id);
    return query.exec();
}

// 更新串口配置
bool SqliteManage::updateSerialConfig(const SerialConfig &config)
{
    QSqlQuery query(m_db);
    query.prepare("UPDATE device_settings SET port_name = :port_name, baud_rate = :baud_rate, "
                  "data_bits = :data_bits, parity = :parity, stop_bits = :stop_bits, flow_control = :flow_control "
                  "WHERE id = :id");
    query.bindValue(":id", config.id);
    query.bindValue(":port_name", config.portName);
    query.bindValue(":baud_rate", config.baudRate);
    query.bindValue(":data_bits", static_cast<int>(config.dataBits));
    query.bindValue(":parity", static_cast<int>(config.parity));
    query.bindValue(":stop_bits", static_cast<int>(config.stopBits));
    query.bindValue(":flow_control", static_cast<int>(config.flowControl));
    return query.exec();
}

bool SqliteManage::insertSensorData(int deviceId, double temperature, double humidity, double light)
{
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO data (device_id, temperature, humidity, light, timestamp) "
                  "VALUES (:device_id, :temperature, :humidity, :light, :timestamp)");
    query.bindValue(":device_id", deviceId);
    query.bindValue(":temperature", temperature);
    query.bindValue(":humidity", humidity);
    query.bindValue(":light", light);
    query.bindValue(":timestamp", QDateTime::currentDateTime());
    return query.exec();
}

QSqlQuery SqliteManage::querySensorData(int deviceId, const QDateTime &startTime, const QDateTime &endTime)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT d.data_id, d.device_id, sc.name, d.temperature, d.humidity, d.light, d.timestamp "
                  "FROM data d "
                  "JOIN device_settings sc ON d.device_id = sc.device_id "
                  "WHERE d.timestamp BETWEEN :start AND :end";

    // 添加设备ID条件
    if (deviceId != -1) {  // -1表示全部设备
        sql += " AND d.device_id = :device_id";
    }

    // 统一调用 prepare()
    if (!query.prepare(sql)) {
        qCritical() << "Prepare failed:" << query.lastError().text();
        return query;
    }

    // 绑定参数
    query.bindValue(":start", startTime.toString("yyyy-MM-ddTHH:mm:ss.zzz"));
    query.bindValue(":end", endTime.toString("yyyy-MM-ddTHH:mm:ss.zzz"));
    if (deviceId != -1) {
        query.bindValue(":device_id", deviceId);
    }

    qDebug() << "Bound values:" << query.boundValues(); // 调试输出绑定的值

    if (!query.exec()) {
        qCritical() << "Query failed:" << query.lastError().text();
    }
    return query;
}

bool SqliteManage::getDeviceList(QSqlQuery &query)
{
    query = QSqlQuery(m_db);
    return query.exec("SELECT device_id, name FROM device_settings");
}

int SqliteManage::getUserIdByName(QString &username)
{
    QSqlQuery query(m_db);
    query.prepare("SELECT id FROM users WHERE name = :name");
    query.bindValue(":name", username);

    if (!query.exec()) {
        qDebug() << "Query failed:" << query.lastError().text();
        return -1;
    }

    if (query.next()) {  // 检查是否有结果
        return query.value(0).toInt();
    } else {
        return -1;  // 无匹配记录
    }
}
